﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace _52fzc.SqlServerRepository
{
    public class SqlServerDBMM
    {
        private static SqlConnection _conn;
        public static SqlConnection Conn
        {
            get
            {
                if (_conn == null)
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                    _conn = new SqlConnection(connectionString);
                }
                return _conn;
            }
        }

        /// <summary>
        /// 查询表
        /// </summary>
        /// <param name="sql">查询用 sql 语句</param>
        /// <returns>填充好的 dt</returns>
        public DataTable GetTable(string sql)
        {
            SqlConnection con = Conn;
            DataTable dt = new DataTable();
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlDataAdapter dap = new SqlDataAdapter(sql, con);
                dap.Fill(dt);
                con.Close();
                return dt;
            }
            catch (Exception ex)
            {
                string ec = ex.ToString();
                con.Close();
                return null;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 查询表
        /// </summary>
        /// <param name="selectCommand">可以是 Transact SQL select 语句或存储过程</param>
        /// <returns>填充好的 dt</returns>
        public DataTable GetTable(SqlCommand selectCommand)
        {
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(selectCommand);
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);
            return dt;
        }
        public DataTable GetTable(string sql, SqlParameter[] pars)
        {
            SqlConnection con = Conn;
            SqlCommand cmd = new SqlCommand(sql, con);
            DataTable dt = new DataTable();
            foreach (SqlParameter sp in pars)
            {
                cmd.Parameters.Add(sp);
            }
            SqlDataAdapter dap = new SqlDataAdapter(cmd);
            try
            {
                dap.Fill(dt);
                con.Close();
                return dt;
            }
            catch (Exception ex)
            {
                string ec = ex.ToString();
                con.Close();
                return null;
            }
        }

        private SqlTransaction tran;
        private Exception err;

        public void BeginTran()
        {
            if (Conn.State == ConnectionState.Closed)
            {
                Conn.Open();
            }
            tran = Conn.BeginTransaction();
            Conn.Close();
            err = null;
        }

        public Exception Execute(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Conn);
            cmd.Connection.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return ex;
            }
            finally
            {
                cmd.Connection.Close();
            }
            return null;
        }

        public Exception Execute(string sql, SqlParameter[] pars)
        {
            SqlCommand cmd = Conn.CreateCommand();
            cmd.CommandText = sql;
            foreach (SqlParameter sp in pars)
            {
                cmd.Parameters.Add(sp);
            }
            cmd.Connection.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return ex;
            }
            finally
            {
                cmd.Connection.Close();
            }
            return null;
        }
        public Exception ExecuteSp(string sql, SqlParameter[] pars)
        {
            SqlCommand cmd = Conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sql;
            foreach (SqlParameter sp in pars)
            {
                cmd.Parameters.Add(sp);
            }
            cmd.Connection.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return ex;
            }
            finally
            {
                cmd.Connection.Close();
            }
            return null;
        }

        public Exception EndTran()
        {
            Exception e = err;
            if (e == null)
                tran.Commit();
            else
                tran.Rollback();
            Conn.Close();
            tran = null;
            err = null;

            return e;
        }

        public void DbOpen()
        {
            try
            {
                Conn.Open();
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public void DbClose()
        {
            try
            {
                Conn.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public DataSet MySet(string sql, string tableName)
        {
            DataSet tempSet = new DataSet();
            SqlDataAdapter myRead = new SqlDataAdapter(MyCommand(sql));
            myRead.Fill(tempSet, tableName);
            return tempSet;
        }
        public SqlCommand MyCommand(string sql)
        {
            SqlCommand tempCommand = new SqlCommand(sql, Conn);
            return tempCommand;
        }
        public int CmdDataAction(string sql, string[] dbValue, string[] dbType, string[] dbPar)
        {
            int temp = 0;
            SqlCommand tempCommand = CmdCommand(sql, dbValue, dbPar, dbType);
            try
            {
                temp = Convert.ToInt16(tempCommand.ExecuteNonQuery().ToString());
            }
            catch
            {
                temp = 0;
            }
            return temp;
        }
        public SqlCommand CmdCommand(string sql, string[] dbValue, string[] dbPar, string[] dbType)
        {
            SqlCommand tempCommand = new SqlCommand(sql, Conn);
            string parName = "";
            string parValue = "";
            string parType = "";
            for (int i = 0; i < dbPar.Length; i++)
            {
                parName = dbPar[i];
                parValue = dbValue[i];
                parType = dbType[i];
                tempCommand.Parameters.Add(parName, parType);
                tempCommand.Parameters[parName].Value = parValue;
            }
            return tempCommand;
        }
        /// <summary>
        /// 通用存储过程调用
        /// </summary>
        /// <param name="pname">存储过程名</param>
        /// <param name="prams">参数(可以通过MakeParams生成)</param>
        /// <returns></returns>
        public Exception ExecuteProc(string pname, ArrayList prams)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = pname;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = SqlServerDBMM.Conn;

            for (int i = 0; i < prams.Count; i++)
            {
                SqlParameter prarm = (SqlParameter)prams[i];
                cmd.Parameters.Add(prarm);
            }
            cmd.Connection.Open();
            SqlTransaction tran = cmd.Connection.BeginTransaction();
            cmd.Transaction = tran;
            try
            {
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return ex;
            }
            finally
            {
                cmd.Connection.Close();
            }
            return null;
        }
    }
}
